﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;

namespace KCL.Code.Data
{
    public class OrderDebtDAO : BaseDAO
    {
        public List<OrderDebtView> GetOrderDebtViews()
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var lstdebtViews = ctx.ExecuteQuery<OrderDebtView>(@"
                    SELECT R1.code [CustomerCode], R1.name [CustomerName], 
	                    R2.code [OrderCode], R3.payTurn [PayTurn], R3.payDate [PayDate], 
	                    R3.payAmount [PayAmount], R2.TotalPrice, 
	                    R2.TotalPrice - (SELECT SUM(R.payAmount)
					                    FROM OrderDebt R
					                    WHERE R.orderCode = R3.orderCode
						                    AND R.payTurn <= R3.payTurn) Remain
                    FROM [Customer] R1, [Order] R2, [OrderDebt] R3
                    WHERE R1.id = R2.customerId
	                    AND R2.code = R3.orderCode
                    ORDER BY [CustomerCode], [CustomerName], [OrderCode], [PayTurn]");
                return lstdebtViews.AsEnumerable().ToList<OrderDebtView>();
            }
            catch (Exception)
            {
                return null;
            }
        }

        public bool Insert(OrderDebt od)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                ctx.OrderDebts.InsertOnSubmit(od);
                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Update(OrderDebt od)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                var orderDebtToUpdate = (from odebt in ctx.OrderDebts
                                         where odebt.orderCode == od.orderCode
                                         && odebt.payTurn == od.payTurn
                                         select odebt).FirstOrDefault();
                orderDebtToUpdate.orderCode = od.orderCode;
                orderDebtToUpdate.payTurn = od.payTurn;
                orderDebtToUpdate.payAmount = od.payAmount;
                orderDebtToUpdate.payDate = od.payDate;
                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Delete(OrderDebt od)
        {
            try
            {
                KCLDataContext ctx = new KCLDataContext();
                ctx.OrderDebts.DeleteOnSubmit(ctx.OrderDebts.Where(
                    odebt => odebt.orderCode == od.orderCode
                        && odebt.payTurn == od.payTurn).FirstOrDefault());
                ctx.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}